﻿
CREATE PROCEDURE [dbo].[fares_FiltreazaListaFisaMagazie] 
	-- Add the parameters for the stored procedure here
	@filtruArt varchar(8000),
	@laData datetime,
	@CodUnitate smallint
AS
BEGIN
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT * FROM
    (
    SELECT  td.Denumire+'('+td.TipOperatie + ')' Denumire, art.IDArticol,COALESCE(docDetSursa.ValoareUnitara,docDet.ValoareUnitara) ValoareUnitara,doc.IDDocument,
    art.Denumire DenumireArticol,um.Denumire DenumireUM,
    doc.DataDoc,doc.NrDoc,
    case when td.TipOperatie like '%I%' then docDet.Cantitate else 0 end Intrari,
    case when td.TipOperatie like '%O%' then 
    CASE WHEN fs.GestiuneCantitativa=1 THEN docDet.Cantitate ELSE COALESCE(docDetL.Cantitate,0) END 
    else 0 end Iesiri,

    coalesce((select SUM(s.StocInitial+s.StocPrecedent) FROM Stocuri s where s.IDArticol=docDet.IDArticol and s.Pret = COALESCE(docDetSursa.ValoareUnitara,docDet.ValoareUnitara)),0)
    + Coalesce((
	Select SUM(Cantitate) FROM 
	DocumenteDetaliu dd 
	JOIN Documente dct on dd.IDDocument=dct.IDDocument
    join TipDocument tipd ON dct.IDTipDocument = tipd.IDTipDocument
    where dd.IDArticol = docDet.IDArticol AND dd.ValoareUnitara = COALESCE(docDetSursa.ValoareUnitara,docDet.ValoareUnitara) and dct.DataCreation<=doc.DataCreation
    and tipd.TipOperatie like '%I%' and tipd.ModificaStoc=1 AND dct.AreDocGestStoc=1),
	0)
     - Coalesce((
	 Select CASE WHEN fs.GestiuneCantitativa=1 THEN SUM(dd.Cantitate) ELSE SUM(ddl.Cantitate) END Cantitate FROM 
	 DocumenteDetaliu dd 
	 JOIN Documente dct on dd.IDDocument=dct.IDDocument
     join TipDocument tipd ON dct.IDTipDocument = tipd.IDTipDocument
	 left outer join DocumenteDetaliuLink ddl ON dd.IDDocumentDetaliu = ddl.IDDocDetaliuDest
	 left outer join DocumenteDetaliu dds ON ddl.IDDocDetaliuSursa = dds.IDDocumentDetaliu
     where dd.IDArticol = docDet.IDArticol AND 
     ((fs.GestiuneCantitativa=1) OR dds.ValoareUnitara = COALESCE(docDetSursa.ValoareUnitara,docDet.ValoareUnitara)) and dct.DataCreation<=doc.DataCreation
     and tipd.TipOperatie like '%O%' and tipd.ModificaStoc=1 AND dct.AreDocGestStoc=1),0)
     stoc,
    docDet.ValoareTotala,docDet.ValoareTVA,Coalesce(doc.NrDocReceptie,'') NrDocReceptie,doc.DataDocReceptie,
    td.IDTipDocument,coalesce(art.Lungime,0) Lungime,coalesce(art.Latime,0) Latime,Coalesce(art.Grosime,0) Grosime,Coalesce(art.Greutate,0) Greutate,
	doc.DataCreation
    FROM [Articole] art
    LEFT OUTER JOIN [DocumenteDetaliu] docDet ON docDet.IDArticol = art.IDArticol
    JOIN [Documente] doc ON doc.IDDocument = docDet.IDDocument
    JOIN [TipDocument] td ON doc.IDTipDocument = td.IDTipDocument AND td.ModificaStoc=1
    JOIN UnitatiMasura um ON art.IDUnitateMasura = um.IDUnitateMasura
	LEFT OUTER JOIN [DocumenteDetaliuLink] docDetL ON docDetL.IDDocDetaliuDest = docDet.IDDocumentDetaliu AND docDetL.Cantitate<>0
	LEFT OUTER JOIN [DocumenteDetaliu] docDetSursa ON docDetL.IDDocDetaliuSursa = docDetSursa.IDDocumentDetaliu 
	JOIN [FaresSetup] fs ON art.CodUnitate = fs.CodUnitate

    WHERE 
    art.CodUnitate = @CodUnitate AND doc.AreDocGestStoc=1 AND
    (LEN(LTRIM(RTRIM(@filtruArt)))=0 OR (art.IDArticol IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtruArt,',') fart)))
    )
    v
END